package com.xlh.util;

import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.google.common.collect.Lists;
import com.google.common.collect.Table;
import com.xlh.dto.course.SkillDTO;
import com.xlh.enums.LocalResourcesEnum;
import com.xlh.enums.courseTest.ExcelCell;
import com.xlh.exception.common.GlobalException;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author cheer
 */

public class ExcelsUtil {

    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelsUtil.class);

    private ExcelsUtil() {
    }

    public static <T> List<T> readExcelData(MultipartFile file, Map<String, String> headerAlias,
                                            int headerRowIndex, int startRowIndex, Class<T> clazz) {
        try (ExcelReader reader = ExcelUtil.getReader(file.getInputStream())) {
            //设置标题别名的目的在于读取的excel标题是中文，一一对应上javabean中的字段。如果excel标题就是字段名，则无需设置别名
            reader.setHeaderAlias(headerAlias).setIgnoreEmptyRow(true);
            List<T> list = reader.read(headerRowIndex, startRowIndex, clazz);
            for (int i = list.size() - 1; i >= 0; --i) {
                T object = list.get(i);
                if (BeanUtil.isNull(object)) {
                    list.remove(i);
                }
            }
            return list;
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
        }

        return new ArrayList<T>();
    }

    public static Workbook createSingleWorkBook(Workbook wb, List<Map<String, Object>> list,
                                                String[] keys, String columnNames[], int rowNum,
                                                Table<Integer, Integer, String> table) {

        Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
        sheet.setDefaultRowHeight((short) (600));
        for (int i = 0; i < keys.length; i++) {
            sheet.setColumnWidth((short) i, (short) (35.7 * 150));
        }

        Row row = sheet.createRow((short) rowNum);

        CellStyle cs = wb.createCellStyle();
        CellStyle cs2 = wb.createCellStyle();

        Font f = wb.createFont();
        Font f2 = wb.createFont();

        f.setFontHeightInPoints((short) 12);
        f.setColor(IndexedColors.BLACK.getIndex());
        f.setBold(true);

        f2.setFontHeightInPoints((short) 10);
        f2.setColor(IndexedColors.BLACK.getIndex());

        cs.setFont(f);
        cs.setFillForegroundColor(IndexedColors.AQUA.getIndex());
        cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cs.setBorderLeft(BorderStyle.THIN);
        cs.setBorderRight(BorderStyle.THIN);
        cs.setBorderTop(BorderStyle.THIN);
        cs.setBorderBottom(BorderStyle.THIN);
        cs.setVerticalAlignment(VerticalAlignment.CENTER);
        cs.setAlignment(HorizontalAlignment.CENTER);
        cs.setWrapText(true);

        cs2.setFont(f2);
        cs2.setBorderLeft(BorderStyle.THIN);
        cs2.setBorderRight(BorderStyle.THIN);
        cs2.setBorderTop(BorderStyle.THIN);
        cs2.setBorderBottom(BorderStyle.THIN);
        cs2.setVerticalAlignment(VerticalAlignment.CENTER);
        cs2.setAlignment(HorizontalAlignment.CENTER);
        cs2.setWrapText(true);
        for (int i = 0; i < columnNames.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(columnNames[i]);
            cell.setCellStyle(cs);
        }

        int rowDataNum = rowNum + 1;
        for (int i = 1; i < list.size(); i++, rowDataNum++) {
            Row row1 = sheet.createRow((short) rowDataNum);
            for (short j = 0; j < keys.length; j++) {
                Cell cell = row1.createCell(j);
                cell.setCellValue(list.get(i).get(keys[j]) == null ? " " : list.get(i).get(keys[j]).toString());
                cell.setCellStyle(cs2);
            }
        }

        if (table != null && table.size() > 0) {
            table.rowKeySet().forEach(rowKey -> {
                Row row1 = sheet.createRow(rowKey);
                Map<Integer, String> cellMap = table.row(rowKey);
                cellMap.forEach((cellKey, value) -> {
                    Cell cellInfo = row1.createCell(cellKey);
                    cellInfo.setCellValue(value);
                    cellInfo.setCellStyle(cs2);
                });
            });
        }

        return wb;
    }

    public static Workbook createSingleWorkBook(Workbook wb, List<Map<String, Object>> list,
                                                String[] keys, String columnNames[]) {
        return createSingleWorkBook(wb, list, keys, columnNames, 0, null);
    }

    public static Workbook createSingleWorkBookExperiment(String courseName, String chapterName, String className, Workbook wb, List<Map<String, Object>> list,
                                                          String[] keys, String columnNames[]) {

        Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
        for (int i = 0; i < keys.length; i++) {
            sheet.setColumnWidth((short) i, (short) (35.7 * 170));
        }

        Row row = sheet.createRow((short) 0);

        CellStyle cs = wb.createCellStyle();
        CellStyle cs2 = wb.createCellStyle();
        CellStyle cs3 = wb.createCellStyle();

        Font f = wb.createFont();
        Font f2 = wb.createFont();
        Font f3 = wb.createFont();

        f.setFontHeightInPoints((short) 10);
        f.setColor(IndexedColors.BLACK.getIndex());
        f.setBold(true);

        f2.setFontHeightInPoints((short) 10);
        f2.setColor(IndexedColors.BLACK.getIndex());


        f3.setFontHeightInPoints((short) 12);
        f3.setColor(IndexedColors.WHITE.getIndex());
        f3.setFontName("宋体");
        f3.setBold(true);

        cs.setFont(f);
        cs.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cs.setBorderLeft(BorderStyle.THIN);
        cs.setBorderRight(BorderStyle.THIN);
        cs.setBorderTop(BorderStyle.THIN);
        cs.setBorderBottom(BorderStyle.THIN);
        cs.setAlignment(HorizontalAlignment.CENTER);

        cs2.setFont(f2);
        cs2.setBorderLeft(BorderStyle.THIN);
        cs2.setBorderRight(BorderStyle.THIN);
        cs2.setBorderTop(BorderStyle.THIN);
        cs2.setBorderBottom(BorderStyle.THIN);
        cs2.setAlignment(HorizontalAlignment.CENTER);


        cs3.setFont(f3);
        cs3.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());
        cs3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cs3.setBorderLeft(BorderStyle.THIN);
        cs3.setBorderRight(BorderStyle.THIN);
        cs3.setBorderTop(BorderStyle.THIN);
        cs3.setBorderBottom(BorderStyle.THIN);
        cs3.setAlignment(HorizontalAlignment.LEFT);
        Cell cell1 = row.createCell(0);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
        cell1.setCellValue(StringUtils.join("课程:", courseName + "  班级:" + className + "  章节:" + chapterName, " 学生实验详情统计"));
        cell1.setCellStyle(cs3);
        Row row1 = sheet.createRow((short) 1);
        for (int i = 0; i < columnNames.length; i++) {
            Cell cell = row1.createCell(i);
            cell.setCellValue(columnNames[i]);
            cell.setCellStyle(cs);
        }
        for (short i = 1; i < list.size(); i++) {
            Row row2 = sheet.createRow((short) i + 1);
            for (short j = 0; j < keys.length; j++) {
                Cell cell = row2.createCell(j);
                cell.setCellValue(list.get(i).get(keys[j]) == null ? " " : list.get(i).get(keys[j]).toString());
                cell.setCellStyle(cs2);
            }
        }
        return wb;
    }

    public static Workbook createSingleWorkBookExperimentPaper(String paperName, String className, Workbook wb, List<Map<String, Object>> list,
                                                               String[] keys, String columnNames[]) {

        Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
        for (int i = 0; i < keys.length; i++) {
            sheet.setColumnWidth((short) i, (short) (35.7 * 170));
        }

        Row row = sheet.createRow((short) 0);

        CellStyle cs = wb.createCellStyle();
        CellStyle cs2 = wb.createCellStyle();
        CellStyle cs3 = wb.createCellStyle();

        Font f = wb.createFont();
        Font f2 = wb.createFont();
        Font f3 = wb.createFont();

        f.setFontHeightInPoints((short) 10);
        f.setColor(IndexedColors.BLACK.getIndex());
        f.setBold(true);

        f2.setFontHeightInPoints((short) 10);
        f2.setColor(IndexedColors.BLACK.getIndex());


        f3.setFontHeightInPoints((short) 12);
        f3.setColor(IndexedColors.WHITE.getIndex());
        f3.setFontName("宋体");
        f3.setBold(true);

        cs.setFont(f);
        cs.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cs.setBorderLeft(BorderStyle.THIN);
        cs.setBorderRight(BorderStyle.THIN);
        cs.setBorderTop(BorderStyle.THIN);
        cs.setBorderBottom(BorderStyle.THIN);
        cs.setAlignment(HorizontalAlignment.CENTER);

        cs2.setFont(f2);
        cs2.setBorderLeft(BorderStyle.THIN);
        cs2.setBorderRight(BorderStyle.THIN);
        cs2.setBorderTop(BorderStyle.THIN);
        cs2.setBorderBottom(BorderStyle.THIN);
        cs2.setAlignment(HorizontalAlignment.CENTER);


        cs3.setFont(f3);
        cs3.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());
        cs3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cs3.setBorderLeft(BorderStyle.THIN);
        cs3.setBorderRight(BorderStyle.THIN);
        cs3.setBorderTop(BorderStyle.THIN);
        cs3.setBorderBottom(BorderStyle.THIN);
        cs3.setAlignment(HorizontalAlignment.LEFT);
        Cell cell1 = row.createCell(0);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
        cell1.setCellValue(StringUtils.join("  班级:" + className + "  " + paperName, " 试卷考试成绩详情"));
        cell1.setCellStyle(cs3);
        Row row1 = sheet.createRow((short) 1);
        for (int i = 0; i < columnNames.length; i++) {
            Cell cell = row1.createCell(i);
            cell.setCellValue(columnNames[i]);
            cell.setCellStyle(cs);
        }
        for (short i = 1; i < list.size(); i++) {
            Row row2 = sheet.createRow((short) i + 1);
            for (short j = 0; j < keys.length; j++) {
                Cell cell = row2.createCell(j);
                cell.setCellValue(list.get(i).get(keys[j]) == null ? "0" : list.get(i).get(keys[j]).toString());
                cell.setCellStyle(cs2);
            }
        }
        return wb;
    }

    public static Workbook createSingleWorkBookPractice(Workbook wb, List<Map<String, Object>> list,
                                                        String[] keys, String columnNames[]) {

        Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
        for (int i = 0; i < keys.length; i++) {
            sheet.setColumnWidth((short) i, (short) (35.7 * 170));
        }

        Row row = sheet.createRow((short) 0);

        CellStyle cs = wb.createCellStyle();
        CellStyle cs2 = wb.createCellStyle();
        CellStyle cs3 = wb.createCellStyle();

        Font f = wb.createFont();
        Font f2 = wb.createFont();
        Font f3 = wb.createFont();

        f.setFontHeightInPoints((short) 10);
        f.setColor(IndexedColors.BLACK.getIndex());
        f.setBold(true);

        f2.setFontHeightInPoints((short) 10);
        f2.setColor(IndexedColors.BLACK.getIndex());


        f3.setFontHeightInPoints((short) 12);
        f3.setColor(IndexedColors.WHITE.getIndex());
        f3.setFontName("宋体");
        f3.setBold(true);

        cs.setFont(f);
        cs.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cs.setBorderLeft(BorderStyle.THIN);
        cs.setBorderRight(BorderStyle.THIN);
        cs.setBorderTop(BorderStyle.THIN);
        cs.setBorderBottom(BorderStyle.THIN);
        cs.setAlignment(HorizontalAlignment.CENTER);

        cs2.setFont(f2);
        cs2.setBorderLeft(BorderStyle.THIN);
        cs2.setBorderRight(BorderStyle.THIN);
        cs2.setBorderTop(BorderStyle.THIN);
        cs2.setBorderBottom(BorderStyle.THIN);
        cs2.setAlignment(HorizontalAlignment.CENTER);


        cs3.setFont(f3);
        cs3.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());
        cs3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cs3.setBorderLeft(BorderStyle.THIN);
        cs3.setBorderRight(BorderStyle.THIN);
        cs3.setBorderTop(BorderStyle.THIN);
        cs3.setBorderBottom(BorderStyle.THIN);
        cs3.setAlignment(HorizontalAlignment.LEFT);
        Cell cell1 = row.createCell(0);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
        cell1.setCellValue(StringUtils.join("课程:", list.get(1).get("courseName"), " 班级:", list.get(1).get("className"), " 章节:", list.get(1).get("chapterName"), "  随堂练习统计"));
        cell1.setCellStyle(cs3);
        Row row1 = sheet.createRow((short) 1);
        for (int i = 0; i < columnNames.length; i++) {
            Cell cell = row1.createCell(i);
            cell.setCellValue(columnNames[i]);
            cell.setCellStyle(cs);
        }
        for (short i = 1; i < list.size(); i++) {
            Row row2 = sheet.createRow((short) i + 1);
            for (short j = 0; j < keys.length; j++) {
                Cell cell = row2.createCell(j);
                cell.setCellValue(list.get(i).get(keys[j]) == null ? " " : list.get(i).get(keys[j]).toString());
                cell.setCellStyle(cs2);
            }
        }
//       cell1.setCellValue(StringUtils.join(list.get(0).get("courseName"),"课程",list.get(0).get("className"),"班级",list.get(0).get("chapterName"),"章节"," ","随堂练习统计"));

        return wb;
    }

    public static void ExcelOutputStream(List<List<Map<String, Object>>> list, List<String[]> keys,
                                         List<String[]> columnNames, String fileName, List<Integer> rowNums,
                                         List<Table<Integer, Integer, String>> tables,
                                         HttpServletRequest request, HttpServletResponse response) {
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            Workbook wb = new HSSFWorkbook();
            for (int i = 0; i < list.size(); i++) {
                wb = createSingleWorkBook(wb, list.get(i), keys.get(i), columnNames.get(i),
                        CollectionUtils.isEmpty(rowNums) ? 0 : rowNums.get(i),
                        CollectionUtils.isEmpty(tables) ? null : tables.get(i));
            }
            wb.write(os);
        } catch (IOException e) {
            LOGGER.error(e.getMessage(), e);
        }
        ExcelOutputStream(fileName, request, response, os);
    }

    public static void ExcelOutputStream(List<List<Map<String, Object>>> list, List<String[]> keys,
                                         List<String[]> columnNames, String fileName,
                                         HttpServletRequest request, HttpServletResponse response) {
        ExcelOutputStream(list, keys, columnNames, fileName, null, null, request, response);
    }

    public static void ExcelSingleOutputStreamPractice(List<Map<String, Object>> list, String[] keys, String[] columnNames,
                                                       String fileName, HttpServletRequest request, HttpServletResponse response) {
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            createSingleWorkBookPractice(new HSSFWorkbook(), list, keys, columnNames).write(os);
        } catch (IOException e) {
            LOGGER.error(e.getMessage(), e);
        }
        ExcelOutputStream(fileName, request, response, os);
    }

    public static void ExcelSingleOutputStream(List<Map<String, Object>> list, String[] keys, String[] columnNames,
                                               String fileName, HttpServletRequest request, HttpServletResponse response) {
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            createSingleWorkBook(new HSSFWorkbook(), list, keys, columnNames).write(os);
        } catch (IOException e) {
            LOGGER.error(e.getMessage(), e);
        }
        ExcelOutputStream(fileName, request, response, os);
    }

    public static void ExcelSingleOutputStreamExperiment(String courseName, String chapterName, String className, List<Map<String, Object>> list, String[] keys, String[] columnNames,
                                                         String fileName, HttpServletRequest request, HttpServletResponse response) {
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            createSingleWorkBookExperiment(courseName, chapterName, className, new HSSFWorkbook(), list, keys, columnNames).write(os);
        } catch (IOException e) {
            LOGGER.error(e.getMessage(), e);
        }
        ExcelOutputStream(fileName, request, response, os);
    }

    public static void ExcelSingleOutputStreamExperimentPaper(String paperName, String className, List<Map<String, Object>> list, String[] keys, String[] columnNames,
                                                              String fileName, HttpServletRequest request, HttpServletResponse response) {
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            createSingleWorkBookExperimentPaper(paperName, className, new HSSFWorkbook(), list, keys, columnNames).write(os);
        } catch (IOException e) {
            LOGGER.error(e.getMessage(), e);
        }
        ExcelOutputStream(fileName, request, response, os);
    }

    private static void ExcelOutputStream(String fileName, HttpServletRequest request, HttpServletResponse response, ByteArrayOutputStream os) {
        byte[] content = os.toByteArray();
        InputStream is = new ByteArrayInputStream(content);

        response.reset();
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName).getBytes(), "ISO-8859-1") + ".xls");
        } catch (UnsupportedEncodingException e) {
            LOGGER.error(e.getMessage(), e);
        }
        ServletOutputStream out = null;
        try {
            out = response.getOutputStream();
        } catch (IOException e) {
            LOGGER.error(e.getMessage(), e);
        }
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;

        try {
            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[2048];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (IOException e) {
            LOGGER.error(e.getMessage(), e);
        } finally {
            if (bis != null)
                try {
                    bis.close();
                } catch (IOException e) {
                    LOGGER.error(e.getMessage(), e);
                }
            if (bos != null)
                try {
                    bos.close();
                } catch (IOException e) {
                    LOGGER.error(e.getMessage(), e);
                }
        }
    }

    public static <T> Workbook createWorkBook(List<T> resultList, Class<? extends ExcelCell> clazz) throws IllegalAccessException {
        Workbook wb = new HSSFWorkbook();

        Sheet sheet = wb.createSheet();

        ExcelCell[] columns = clazz.getEnumConstants();

        Row row = sheet.createRow(0);

        for (ExcelCell column : columns) {
            sheet.setColumnWidth(column.getColumn(), 256 * column.getColumnLength() + 184);
            row.createCell(column.getColumn()).setCellValue(column.getColumnInfo());
        }

        for (int i = 0; i < resultList.size(); i++) {
            T obj = resultList.get(i);

            Field[] fields = obj.getClass().getDeclaredFields();

            Row contentRow = sheet.createRow(i + 1);
            for (int j = 0; j < columns.length; j++) {
                ExcelCell column = columns[j];

                Field field = fields[j];
                field.setAccessible(true);

                contentRow.createCell(column.getColumn()).setCellValue(field.get(obj).toString());
            }
        }

        return wb;
    }


    public static List<SkillDTO> getAllByExcel(MultipartFile file, Integer sheetIndex, Integer StartReadLine) {
        try {
            // 同时支持Excel 2003、2007
            FileInputStream is = new FileInputStream(multipartFileToFile(file)); // 文件流
            Workbook workbook = WorkbookFactory.create(is); // 这种方式 Excel
            List<SkillDTO> list = readExcel(workbook, sheetIndex, StartReadLine, 0);
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return new ArrayList<>();
    }

    public static File multipartFileToFile(MultipartFile file) throws Exception {

        File toFile = null;
        if (file.equals("") || file.getSize() <= 0) {
            file = null;
        } else {
            InputStream ins = null;
            ins = file.getInputStream();
            toFile = new File(file.getOriginalFilename());
            inputStreamToFile(ins, toFile);
            ins.close();
        }
        return toFile;
    }

    //获取流文件
    private static void inputStreamToFile(InputStream ins, File file) {
        try {
            OutputStream os = new FileOutputStream(file);
            int bytesRead = 0;
            byte[] buffer = new byte[8192];
            while ((bytesRead = ins.read(buffer, 0, 8192)) != -1) {
                os.write(buffer, 0, bytesRead);
            }
            os.close();
            ins.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 删除本地临时文件
     *
     * @param file
     */
    public static void delteTempFile(File file) {
        if (file != null) {
            File del = new File(file.toURI());
            del.delete();
        }
    }


    public static String uploadExeSkill(MultipartFile file, Integer skillId) {
        if (file == null) return null;

        String fileName = file.getOriginalFilename();
        String suffix = StrUtil.subAfter(fileName, ".", true);
        String sufbix = StrUtil.subBefore(fileName, ".", true);
        String uuid = "GW-" + String.format("%05d", skillId);
        String newFileNameRemoveSuffix =
                StringUtils.join(sufbix, uuid, ".", suffix);

        String filePath = LocalResourcesEnum.SYSTEM_FILE.getLocalUploadPath(newFileNameRemoveSuffix);
        // 创建文件目录
        if (new File(filePath).exists()) {
            FileUtil.del(filePath);
        }

        // 写入文件
        File newFile = new File(filePath);
        try {
            file.transferTo(newFile);
        } catch (IOException e) {
            System.out.println(e.getMessage());
            throw new GlobalException("Excel上传失败");
        }

        return newFileNameRemoveSuffix;
    }

    private static List<SkillDTO> readExcel(Workbook wb, int sheetIndex, int startReadLine, int tailLine) {
        Sheet sheet = wb.getSheetAt(sheetIndex);
        Row row = null;
        String[] res = new String[sheet.getLastRowNum() - tailLine + 1];
        List list = Lists.newArrayList();
        try {


            for (int i = startReadLine; i < sheet.getLastRowNum() - tailLine + 1; i++) {

                row = sheet.getRow(i);
                res[i] = "";
                SkillDTO skillDTO = new SkillDTO();
                int count = 0;
                for (Cell c : row) {
                    count++;
//                boolean isMerge = isMergedRegion(sheet, i, c.getColumnIndex());
//                // 判断是否具有合并单元格
//
//                if (isMerge) {
//                    String rs = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex());
//
//                    //System.out.print(rs + "_"+ row.getRowNum()+"_"+c.getColumnIndex() +"_");
//                    res[i] = rs;
//                } else {
//                    //System.out.print(c.getRichStringCellValue() + "");
//                    res[i] = getCellValue(c);
//                }
                    res[i] = getCellValue(c);
                    switch (count) {
                        case 1: // 岗位
                            skillDTO.setPost(res[i]);
                            if ("".equals(res[i]) && i == 1) return new ArrayList<>();
                            break;
                        case 2: // 描述
                            skillDTO.setDescribe(res[i]);
                            break;
                        case 3: // 技能模块
                            skillDTO.setSkillModule(replaceBlank(res[i]));
                            break;
                        case 4: // 子技能
                            skillDTO.setSubSkill(replaceBlank(res[i]));
                            break;
                        case 5: // 技能点
                            skillDTO.setKnowledgePoints(replaceBlank(res[i]));
                            break;
                        case 6: // 课程名称
                            boolean isMerge = isMergedRegion(sheet, i, c.getColumnIndex());
                            // 判断是否具有合并单元格

                            if (isMerge) {
                                String rs = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex());

                                //System.out.print(rs + "_"+ row.getRowNum()+"_"+c.getColumnIndex() +"_");
                                res[i] = rs;
                            } else {
                                //System.out.print(c.getRichStringCellValue() + "");
                                res[i] = getCellValue(c);
                            }
                            skillDTO.setCourseName(replaceBlank(res[i]));
                            break;
                    }
                    if (count == 7) {
                        if (!"".equals(res[i])) return new ArrayList<>();
                        break;
                    }
                }
                list.add(skillDTO);

            }
            return list;
        } catch (Exception e) {
            throw new GlobalException("请检查上传的文件是否正确");
        }

    }

    private static String replaceBlank(String str) {
        String dest = "";
        if (str != null) {
            Pattern p = Pattern.compile("\\s*|\t|\r|\n");
            Matcher m = p.matcher(str);
            dest = m.replaceAll("");
        }
        return dest;
    }

    private static boolean isMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }

    public static String getMergedRegionValue(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if (row >= firstRow && row <= lastRow) {

                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell);
                }
            }
        }

        return null;
    }

    private static String getCellValue(Cell cell) {
        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
        String cellValue = "";
        int cellType = cell.getCellType();
        switch (cellType) {
            case Cell.CELL_TYPE_STRING: // 文本
                cellValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_NUMERIC: // 数字、日期
                if (DateUtil.isCellDateFormatted(cell)) {
                    cellValue = fmt.format(cell.getDateCellValue()); // 日期型
                } else {
                    cellValue = String.valueOf((int) cell.getNumericCellValue()); // 数字
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN: // 布尔型
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_BLANK: // 空白
                cellValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_ERROR: // 错误
                cellValue = "错误";
                break;
            case Cell.CELL_TYPE_FORMULA: // 公式
                cellValue = "错误";
                break;
            default:
                cellValue = "错误";
        }
        return cellValue;
    }


}
